package com.yingdong.tools;

import java.io.File;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.eclipse.core.resources.IFile;

public class Excel2007Reader {

	public static <T> List<T> readDataFromFileJava(String path, Class<T> claz) throws Exception {
		List<T> result = new ArrayList<>();

		File file = new File(path);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
		try {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);

			int rowstart = xssfSheet.getFirstRowNum();
			if (rowstart != 0)
				throw new Exception("第一行没数据，表错了,策划拖出去打");
			int rowEnd = xssfSheet.getLastRowNum();
			if (rowEnd < 1)
				throw new Exception("字段类型数据没配，表错了,策划拖出去打");
			if (rowEnd < 2)
				throw new Exception("注释没加，表错了,策划拖出去打");
			if (rowEnd < 3)
				throw new Exception("注释没加，表错了,策划拖出去打");
			XSSFRow row = xssfSheet.getRow(0);

			int cellStart = row.getFirstCellNum();
			int cellEnd = row.getLastCellNum();
			String[] keys = new String[cellEnd - cellStart + 1];
			for (int k = cellStart; k <= cellEnd; k++) {
				XSSFCell cell = row.getCell(k);
				if (cell == null)
					continue;

				switch (cell.getCellType()) {

				case HSSFCell.CELL_TYPE_STRING: // 字符串
					keys[k] = (cell.getStringCellValue().trim());
					break;

				default:
					throw new Exception("有其他类型数据，表错了,策划拖出去打");
				}

			}
			row = xssfSheet.getRow(1);

			String[] types = new String[cellEnd - cellStart + 1];
			for (int k = cellStart; k <= cellEnd; k++) {
				XSSFCell cell = row.getCell(k);
				if (cell == null)
					continue;

				switch (cell.getCellType()) {

				case HSSFCell.CELL_TYPE_STRING: // 字符串
					types[k] = (cell.getStringCellValue().trim().toLowerCase());
					break;

				default:
					throw new Exception("有其他类型数据，表错了,策划拖出去打");
				}

			}

			for (int i = 4; i < rowEnd + 1; i++) {
				row = xssfSheet.getRow(i);
				if (row == null)
					continue;
				T instance = claz.newInstance();
				for (int k = cellStart; k <= cellEnd; k++) {
					XSSFCell cell = row.getCell(k);
					if (cell != null)
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);

					Object value = null;
					String key = null;

					key = keys[k];
					if (key == null)
						continue;
					switch (types[k]) {
					case "int":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Integer(0);
						else
							value = Integer.decode(cell.getStringCellValue().trim());

						break;
					case "string":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = "";
						else
							value = cell.getStringCellValue().trim();

						break;
					case "float":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Float(0);
						else
							value = Float.parseFloat(cell.getStringCellValue().trim());
						break;
					case "double":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Double(0);
						else
							value = Double.parseDouble(cell.getStringCellValue().trim());
						break;
					case "long":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Long(0);
						else
							value = Long.parseLong(cell.getStringCellValue().trim());
						break;
					case "boolean":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Boolean(false);
						else {
							if (cell.getStringCellValue().trim().equals("0"))
								value = new Boolean(false);
							else
								value = new Boolean(true);
						}
						break;
					default:
						break;
					}
					Field field = claz.getDeclaredField(key);
					field.setAccessible(true);
					field.set(instance, value);

				}
				result.add(instance);
			}

			return result;
		} finally {
			// xssfWorkbook.close();
		}
	}

	public static <T> Map<Integer, T> readDataFromFileJavaMap(String path, Class<T> claz) throws Exception {
		Map<Integer, T> result = new HashMap<>();

		File file = new File(path);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
		try {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);

			int rowstart = xssfSheet.getFirstRowNum();
			if (rowstart != 0)
				throw new Exception("第一行没数据，表错了,策划拖出去打");
			int rowEnd = xssfSheet.getLastRowNum();
			if (rowEnd < 1)
				throw new Exception("字段类型数据没配，表错了,策划拖出去打");
			if (rowEnd < 2)
				throw new Exception("注释没加，表错了,策划拖出去打");
			if (rowEnd < 3)
				throw new Exception("注释没加，表错了,策划拖出去打");
			XSSFRow row = xssfSheet.getRow(0);

			int cellStart = row.getFirstCellNum();
			int cellEnd = row.getLastCellNum();
			String[] keys = new String[cellEnd - cellStart + 1];
			for (int k = cellStart; k <= cellEnd; k++) {
				XSSFCell cell = row.getCell(k);
				if (cell == null)
					continue;

				switch (cell.getCellType()) {

				case HSSFCell.CELL_TYPE_STRING: // 字符串
					keys[k] = (cell.getStringCellValue().trim());
					break;

				default:
					throw new Exception("有其他类型数据，表错了,策划拖出去打");
				}

			}
			row = xssfSheet.getRow(1);

			String[] types = new String[cellEnd - cellStart + 1];
			for (int k = cellStart; k <= cellEnd; k++) {
				XSSFCell cell = row.getCell(k);
				if (cell == null)
					continue;

				switch (cell.getCellType()) {

				case HSSFCell.CELL_TYPE_STRING: // 字符串
					types[k] = (cell.getStringCellValue().trim().toLowerCase());
					break;

				default:
					throw new Exception("有其他类型数据，表错了,策划拖出去打");
				}

			}

			for (int i = 4; i < rowEnd + 1; i++) {
				row = xssfSheet.getRow(i);
				if (row == null)
					continue;
				T instance = claz.newInstance();
				for (int k = cellStart; k <= cellEnd; k++) {
					XSSFCell cell = row.getCell(k);
					if (cell != null)
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);

					Object value = null;
					String key = null;

					key = keys[k];
					if (key == null)
						continue;
					switch (types[k]) {
					case "int":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Integer(0);
						else
							value = Integer.decode(cell.getStringCellValue().trim());

						break;
					case "string":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = "";
						else
							value = cell.getStringCellValue().trim();

						break;
					case "float":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Float(0);
						else
							value = Float.parseFloat(cell.getStringCellValue().trim());
						break;
					case "double":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Double(0);
						else
							value = Double.parseDouble(cell.getStringCellValue().trim());
						break;
					case "long":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Long(0);
						else
							value = Long.parseLong(cell.getStringCellValue().trim());
						break;
					case "boolean":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Boolean(false);
						else {
							if (cell.getStringCellValue().trim().equals("0"))
								value = new Boolean(false);
							else
								value = new Boolean(true);
						}
						break;
					default:
						break;
					}
					Field field = claz.getDeclaredField(key);
					field.setAccessible(true);
					field.set(instance, value);

				}
				Field f = claz.getDeclaredField("id");
				f.setAccessible(true);
				Integer id = f.getInt(instance);
				result.put(id, instance);
			}

			return result;
		} finally {
			// xssfWorkbook.close();
		}
	}

	public static List<Map<String, Object>> readDataFromFile(String path) throws Exception {
		List<Map<String, Object>> result = new ArrayList<>();
		File file = new File(path);

		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
		try {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);

			int rowstart = xssfSheet.getFirstRowNum();
			if (rowstart != 0)
				throw new Exception("第一行没数据，表错了,策划拖出去打");
			int rowEnd = xssfSheet.getLastRowNum();
			if (rowEnd < 1)
				throw new Exception("字段类型数据没配，表错了,策划拖出去打");
			if (rowEnd < 2)
				throw new Exception("注释没加，表错了,策划拖出去打");
			if (rowEnd < 3)
				throw new Exception("注释没加，表错了,策划拖出去打");
			XSSFRow row = xssfSheet.getRow(0);

			int cellStart = row.getFirstCellNum();
			int cellEnd = row.getLastCellNum();
			String[] keys = new String[cellEnd - cellStart + 1];
			for (int k = cellStart; k <= cellEnd; k++) {
				XSSFCell cell = row.getCell(k);
				if (cell == null)
					continue;
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				switch (cell.getCellType()) {

				case HSSFCell.CELL_TYPE_STRING: // 字符串
					keys[k] = (cell.getStringCellValue().trim());
					break;

				default:
					throw new Exception("有其他类型数据，表错了,策划拖出去打");
				}

			}
			row = xssfSheet.getRow(1);

			String[] types = new String[cellEnd - cellStart + 1];
			for (int k = cellStart; k <= cellEnd; k++) {
				XSSFCell cell = row.getCell(k);
				if (cell == null)
					continue;
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				switch (cell.getCellType()) {

				case HSSFCell.CELL_TYPE_STRING: // 字符串
					types[k] = (cell.getStringCellValue().trim().toLowerCase());
					break;

				default:
					throw new Exception("有其他类型数据，表错了,策划拖出去打");
				}

			}
			row = xssfSheet.getRow(2);

			String[] alias = new String[cellEnd - cellStart + 1];
			for (int k = cellStart; k <= cellEnd; k++) {
				XSSFCell cell = row.getCell(k);
				if (cell == null)
					continue;
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				switch (cell.getCellType()) {

				case HSSFCell.CELL_TYPE_STRING: // 字符串
					alias[k] = (cell.getStringCellValue().trim());
					if (!alias[k].isEmpty()) {
						keys[k] = keys[k] + "|" + alias[k];
					}
					break;

				default:
					throw new Exception("有其他类型数据，表错了,策划拖出去打");
				}

			}
			for (int i = 4; i < rowEnd + 1; i++) {
				row = xssfSheet.getRow(i);
				if (row == null)
					continue;
				Map<String, Object> map = new HashMap<>();
				for (int k = cellStart; k <= cellEnd; k++) {
					XSSFCell cell = row.getCell(k);
					if (cell != null)
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);

					Object value = null;
					String key = null;

					key = keys[k];
					if (key == null)
						continue;
					switch (types[k]) {
					case "int":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Integer(0);
						else
							value = Integer.decode(cell.getStringCellValue().trim());

						break;
					case "string":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = "";
						else
							value = cell.getStringCellValue().trim();

						break;
					case "float":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Float(0);
						else
							value = Float.parseFloat(cell.getStringCellValue().trim());
						break;
					case "double":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Double(0);
						else
							value = Double.parseDouble(cell.getStringCellValue().trim());
						break;
					case "long":
						if (cell == null || cell.getStringCellValue().trim().isEmpty())
							value = new Long(0);
						else
							value = Long.parseLong(cell.getStringCellValue().trim());
						break;

					default:
						break;
					}
					map.put(key, value);

				}
				result.add(map);
			}

			return result;
		} finally {
			// xssfWorkbook.close();
		}
	}

	public static String readClassComment(String path) throws Exception {
		File file = new File(path);

		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
		return xssfWorkbook.getSheetAt(0).getSheetName();
	}

	public static List<TemplateField> readFieldsFromFile(String path) throws Exception {
		List<TemplateField> result = new ArrayList<>();
		File file = new File(path);

		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
		try {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);

			int rowstart = xssfSheet.getFirstRowNum();
			if (rowstart != 0)
				throw new Exception("第一行没数据，表错了,策划拖出去打");
			int rowEnd = xssfSheet.getLastRowNum();
			if (rowEnd < 1)
				throw new Exception("字段类型数据没配，表错了,策划拖出去打");
			if (rowEnd < 2)
				throw new Exception("注释没加，表错了,策划拖出去打");
			if (rowEnd < 3)
				throw new Exception("注释没加，表错了,策划拖出去打");
			XSSFRow row = xssfSheet.getRow(0);

			int cellStart = row.getFirstCellNum();
			int cellEnd = row.getLastCellNum();

			for (int k = cellStart; k <= cellEnd; k++) {
				XSSFCell cell = row.getCell(k);
				if (cell == null)
					continue;
				int ft = cell.getCellType();
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				TemplateField field = new TemplateField();
				result.add(field);
				if (cell.getStringCellValue().trim().equals("id")) {
					field.setOverride("override");
				}
				switch (cell.getCellType()) {

				case HSSFCell.CELL_TYPE_STRING: // 字符串
					field.setName(cell.getStringCellValue().trim());
					if (cell.getCellComment() != null) {

						field.setComment(cell.getCellComment().getString().toString());
					} else {
						field.setComment("");
					}
					break;

				default:
					throw new Exception("有其他类型数据，表错了,策划拖出去打");

				}
				cell.setCellType(ft);

			}
			row = xssfSheet.getRow(1);

			cellStart = row.getFirstCellNum();
			cellEnd = row.getLastCellNum();

			for (int k = cellStart; k <= cellEnd; k++) {
				XSSFCell cell = row.getCell(k);
				if (cell == null)
					continue;
				int ft = cell.getCellType();
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				TemplateField field = result.get(k);
				switch (cell.getCellType()) {

				case HSSFCell.CELL_TYPE_STRING: // 字符串
					field.setType(cell.getStringCellValue().trim());
					if (field.getType().equals("string")) {
						field.setType("String");
					}
					if (field.getType().equals("boolean")) {
						field.setType("Boolean");
					}
					break;

				default:
					throw new Exception("有其他类型数据，表错了,策划拖出去打");
				}
				cell.setCellType(ft);

			}

			row = xssfSheet.getRow(2);

			cellStart = row.getFirstCellNum();
			cellEnd = row.getLastCellNum();

			for (int k = cellStart; k <= cellEnd; k++) {
				XSSFCell cell = row.getCell(k);
				if (cell == null)
					continue;
				int ft = cell.getCellType();
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				TemplateField field = result.get(k);
				switch (cell.getCellType()) {

				case HSSFCell.CELL_TYPE_STRING: // 字符串
					field.setComment(cell.getStringCellValue().trim());
					break;

				default:
					throw new Exception("有其他类型数据，表错了,策划拖出去打");
				}
				cell.setCellType(ft);
			}
			return result;
		} finally {
			xssfWorkbook.close();
		}
	}

}
